package com.tongji.run;

import com.ibeetl.admin.core.util.DateUtil;
import com.ibeetl.admin.core.util.PlatformException;
import com.tongji.utils.DateUtils;
import com.tongji.utils.JxlsUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 职工任务数统计
 */
public class 员工名下超半年浆员采浆次数名单 {

    public static Date dateRangeMonth = DateUtil.getDate("2022-02-01");
    public static String renyuanDateRangeStart = "202201010000000";
    public static String renyuanDateRangeEnd =   "202201312359999";

    public static String root = "E:\\文档\\2022\\每月统计\\" + DateUtils.getMonth(dateRangeMonth) + "月\\";

    public static SQLManager taibangSqlManager() {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://10.1.17.253:3306/plasma?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "plasma", "szrouting2004plasma");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});

        return sqlManager;
    }

    public static void main(String[] args) {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://127.0.0.1:3308/tongji?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "root", "123456");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});

        List<HashMap> staffList = sqlManager.execute(new SQLReady("select * from member", new Object[] {}), HashMap.class);

        Map model = new HashMap();
        model.put("staffList", staffList);
        for (Iterator<HashMap> iterator = staffList.iterator(); iterator.hasNext(); ) {
            HashMap staff =  iterator.next();
            staff.put("dateRange",renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8));
            getStaffChanbannianDonerList(staff);
            getStaffJianyiqiDonerList(staff);
        }
    }


    public static Map getStaffChanbannianDonerList(HashMap staff) {
        SQLManager sqlManager = taibangSqlManager();

        //时间范围内超半年浆员列表
        List<HashMap> chaobannianList = sqlManager.execute(new SQLReady("select * from (\n" +
                "\tselect donor.donorId as dId,donor.donorNo, donor.donorName,donor.phone,left(donor.idAddress,12) as address,left(register.recordDate,8) as firstplasmadate,left(donor.plasmaDate,8) as plasmadate, left(register.registerDate,8) as startDate,date_add(left(register.registerDate,8), interval 60 day) as endDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate > startDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                "\tfrom t_bims_donor_register as register left join t_bims_donor as donor on register.donorId = donor.donorId where \n" +
                "\t\tchangeless = 'N' and donor.plasmaDate > register.recordDate and registerDate between ? and ? and donor.remark like ?  group by donor.donorNo\n" +
                ") as a order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.get("name") + "%"}), HashMap.class);
        staff.put("donorList",chaobannianList);

        String excelTemplate = "excelTemplates/员工/员工名下超半年浆员名单.xls";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\程序查询\\" + staff.get("name") + "-超半年浆员名单-" + staff.get("name") + "-" + renyuanDateRangeStart.substring(0,8) + "-" + renyuanDateRangeEnd.substring(0,8) + ".xls");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,staff);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

        return null;
    }

    public static Map getStaffJianyiqiDonerList(HashMap staff) {
        SQLManager sqlManager = taibangSqlManager();

        //时间范围内检疫期浆员列表
        List<HashMap> chaobannianList = sqlManager.execute(new SQLReady("select * from (\n" +
                "select b.*,\n" +
                " (select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate > startDate and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                " from (\n" +
                "select *, (TO_DAYS(DATE_FORMAT(left(rDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(payDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donor.donorNo,\n" +
                "\t\t\t\t\tdonor.plasmaDate as pDate, \n" +
                "\t\t\t\t\tdonor.donorId as dId,\n" +
                "\t\t\t\t\tregister.registerDate as rDate,\n" +
                "\t\t\t\t\tdonor.donorName,donor.phone,left(donor.idAddress,12) as address,left(register.recordDate,8) as firstplasmadate,left(donor.plasmaDate,8) as plasmadate,\n" +
                "\t\t\t\t\tleft(register.registerDate,8) as startDate,date_add(left(register.registerDate,8), interval 60 day) as endDate,\n" +
                "\t\t\t\t\t(select pay.payDate from t_bims_expense_payment as pay where pay.donorId = dId and pay.paydate < rDate order by pay.payDate desc limit 0,1) as payDate\t\t\t\n" +
                "  from t_bims_donor_register as register left join t_bims_donor as donor on register.donorId = donor.donorId where  donor.plasmaDate > register.registerDate and changeless != 'N' and registerDate between ? and ? and donor.remark like ?  group by donor.donorNo\n" +
                " ) as a) as b where b.days >= 90 order by b.days desc\n" +
                ") as aa order by aa.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.get("name") + "%"}), HashMap.class);
        staff.put("donorList",chaobannianList);

        String excelTemplate = "excelTemplates/员工/员工名下检疫期浆员名单.xls";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\程序查询\\" + staff.get("name") + "-检疫期浆员名单-" +  "-" + renyuanDateRangeStart.substring(0,8) + "-" + renyuanDateRangeEnd.substring(0,8) + ".xls");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,staff);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

        return null;
    }
}
